﻿Imports System.Data.SqlClient
Imports Tool

Public Class QueryGrid
    Public Event RowDoubleClick(ByVal rowIndex As Integer)

    Private mstrRecordSQL As String
    Private mobjSqlConnection As SqlConnection
    Private mobjRowFilter As New Stack(Of String)
    Private marrSumCoulumns As String()
    Private mdicColumnsWidth As New Dictionary(Of String, Integer)
    Private mdicColumnsVisible As New Dictionary(Of String, Boolean)
    Private mblnColumnFooters As Boolean = True
    Private mstrFrozenColumn As String
    Private mblnIsTransZeroToBlank As Boolean

    Private mstrParentsColumns As String
    Private mstrLocalColumns As String
    Private mstrRelationRowFilter As String
    Private mblnIsInit As Boolean

    Private mdtDataTable As New DataTable
    Private mdvDataView As DataView

    Public Property DataSource As DataTable
        Get
            Return mdtDataTable
        End Get
        Set(value As DataTable)
            If value Is Nothing Then
                mdtDataTable.Clear()
            Else
                mdtDataTable = value
            End If
            Init()
        End Set
    End Property

    Public WriteOnly Property ParentsColumns As String
        Set(value As String)
            mstrParentsColumns = value
        End Set
    End Property

    Public WriteOnly Property LocalColumns As String
        Set(value As String)
            mstrLocalColumns = value
        End Set
    End Property


    Public WriteOnly Property IsTransZeroToBlank As Boolean
        Set(value As Boolean)
            mblnIsTransZeroToBlank = value
        End Set
    End Property


    Public WriteOnly Property Cnn As SqlConnection
        Set(value As SqlConnection)
            mobjSqlConnection = value
        End Set
    End Property
    Public WriteOnly Property RecordSQL As String
        Set(value As String)
            mstrRecordSQL = value
        End Set
    End Property

    Public WriteOnly Property FrozenColumn As String
        Set(value As String)
            mstrFrozenColumn = value
            RefreshUI()
        End Set
    End Property

    Public WriteOnly Property SqlConnection As SqlConnection
        Set(value As SqlConnection)
            mobjSqlConnection = value
        End Set
    End Property

    Public WriteOnly Property ColumnsWidth(ByVal columnsName() As String) As Integer()
        Set(value As Integer())
            If columnsName.Count <> value.Count Then
                MsgBox("列宽设置错误!", MsgBoxStyle.Exclamation, "系统提示")
                Exit Property
            End If
            For i As Integer = LBound(columnsName) To UBound(columnsName)
                If mdicColumnsWidth.ContainsKey(columnsName(i)) Then
                    mdicColumnsWidth.Item(columnsName(i)) = value(i)
                Else
                    mdicColumnsWidth.Add(columnsName(i), value(i))
                End If
            Next
            RefreshUI()
        End Set
    End Property

    Public WriteOnly Property ColumnsVisible(ByVal columnsName() As String) As Boolean()
        Set(value As Boolean())
            If columnsName.Count <> value.Count Then
                MsgBox("列的可见性设置错误!", MsgBoxStyle.Exclamation, "系统提示")
                Exit Property
            End If
            For i As Integer = LBound(columnsName) To UBound(columnsName)
                If mdicColumnsVisible.ContainsKey(columnsName(i)) Then
                    mdicColumnsVisible.Item(columnsName(i)) = value(i)
                Else
                    mdicColumnsVisible.Add(columnsName(i), value(i))
                End If

            Next
            RefreshUI()
        End Set
    End Property

    Public ReadOnly Property ColumnsDefaultCellStyle(ByVal index As Integer) As DataGridViewCellStyle
        Get
            Return DataGridView1.Columns(index).DefaultCellStyle
        End Get
    End Property

    Public ReadOnly Property ColumnsDefaultCellStyle(ByVal columnName As String) As DataGridViewCellStyle
        Get
            Return DataGridView1.Columns(columnName).DefaultCellStyle
        End Get
    End Property

    Public ReadOnly Property RowsDefaultCellStyle(ByVal index As Integer) As DataGridViewCellStyle
        Get
            Return DataGridView1.Rows(index).DefaultCellStyle
        End Get
    End Property

    Public Property ColumnsHeaderText(ByVal index As Integer) As String
        Get
            Return DataGridView1.Columns(index).HeaderText
        End Get
        Set(value As String)
            DataGridView1.Columns(index).HeaderText = value
        End Set
    End Property

    Public Property ColumnsHeaderText(ByVal columnName As String) As String
        Get
            Return DataGridView1.Columns(columnName).HeaderText
        End Get
        Set(value As String)
            DataGridView1.Columns(columnName).HeaderText = value
        End Set
    End Property

    Private Sub test()
        Me.ColumnsWidth({}) = {""}
        'Me.DataGridView1.Columns(0).DefaultCellStyle
        'Me.DataGridView1.Rows(0).Cells(1).FormattedValue = ""


    End Sub

    Public WriteOnly Property SumColumns As String()
        Set(value As String())
            marrSumCoulumns = value
        End Set
    End Property

    Public WriteOnly Property ColumnFooters As Boolean
        Set(value As Boolean)
            mblnColumnFooters = value
            If mblnColumnFooters Then
                FootSumLabel.Visible = True
                Me.DataGridView1.Height = Me.Height - FootSumLabel.Height
            Else
                FootSumLabel.Visible = False
                Me.DataGridView1.Height = Me.Height
            End If
            FootSumLabel.Refresh()
        End Set
    End Property

    Public ReadOnly Property Rows As DataGridViewRowCollection
        Get
            Return DataGridView1.Rows
        End Get
    End Property

    Public ReadOnly Property SelectedRows As DataGridViewSelectedRowCollection
        Get
            Return DataGridView1.SelectedRows
        End Get
    End Property

    Public ReadOnly Property SelectedColumns As DataGridViewSelectedColumnCollection
        Get
            Return DataGridView1.SelectedColumns
        End Get
    End Property

    Public ReadOnly Property SelectedCells As DataGridViewSelectedCellCollection
        Get
            Return DataGridView1.SelectedCells
        End Get
    End Property

    Public Property CurrentRow As DataGridViewRow
        Get
            Return DataGridView1.CurrentRow
        End Get
        Set(value As DataGridViewRow)
            If value Is Nothing Then
                DataGridView1.CurrentCell = Nothing
                Exit Property
            End If
            DataGridView1.CurrentCell = value.Cells(0)
        End Set
    End Property

    Public Sub Init()
        Dim dataAdapter As SqlDataAdapter
        Me.DataGridView1.DataSource = Nothing
        If Len(mstrRecordSQL) = 0 AndAlso mdtDataTable Is Nothing Then Exit Sub
        If Len(mstrRecordSQL) > 0 AndAlso Not mobjSqlConnection Is Nothing Then
            dataAdapter = New SqlDataAdapter(mstrRecordSQL, mobjSqlConnection)
            mdtDataTable.Clear()
            dataAdapter.Fill(mdtDataTable)
        End If
        mobjRowFilter.Clear()
        mdvDataView = mdtDataTable.DefaultView
        mdvDataView.RowFilter = mstrRelationRowFilter
        DataGridView1.DataSource = mdvDataView
        RefreshUI()
        FootSumLabel.Refresh()
        mblnIsInit = True
    End Sub

    Public Sub RefreshUI()
        Try
            For Each keyValue As KeyValuePair(Of String, Integer) In mdicColumnsWidth
                DataGridView1.Columns(keyValue.Key).Width = keyValue.Value
            Next

            For Each keyValue As KeyValuePair(Of String, Boolean) In mdicColumnsVisible
                DataGridView1.Columns(keyValue.Key).Visible = keyValue.Value
            Next
            If Len(mstrFrozenColumn) > 0 Then
                DataGridView1.Columns(mstrFrozenColumn).Frozen = True
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "系统提示")
        End Try

    End Sub
    'Private Sub RefreshFootSumLabel()
    '    Dim x As Integer
    '    Dim i As Integer
    '    Dim strFormat As New StringFormat
    '    strFormat.Alignment = StringAlignment.Far
    '    x = DataGridView1.RowHeadersWidth
    '    For i = 0 To DataGridView1.Columns.Count - 1
    '        x += DataGridView1.Columns(i).Width
    '        If marrSumCoulumns.Contains(DataGridView1.Columns(i).Name) Then
    '            e.Graphics.DrawString(mdtDataTable.Compute("Sum(" & DataGridView1.Columns(i).Name & ")", mstrRowFiter),
    '                    Me.FootSumLabel.Font, Brushes.Black, x - DataGridView1.HorizontalScrollingOffset, 3, strFormat)
    '        End If
    '    Next i
    'End Sub

    Private Sub FootSumLabel_Paint(sender As Object, e As System.Windows.Forms.PaintEventArgs) Handles FootSumLabel.Paint
        Dim x As Integer
        Dim i As Integer
        Dim scrollingOffset As Integer
        Dim frozenWidth As Integer
        Dim dataTable As DataTable
        'Dim dataView As DataView
        Dim objSumValue As Object
        Dim strFormat As New StringFormat
        If Not mblnColumnFooters Then
            Exit Sub
        End If
        If mdvDataView Is Nothing Then Exit Sub
        frozenWidth = GetFrozenWidth()
        dataTable = mdvDataView.ToTable
        strFormat.Alignment = StringAlignment.Far
        If IsArray(marrSumCoulumns) Then
            x = DataGridView1.RowHeadersWidth
            For i = 0 To DataGridView1.Columns.Count - 1
                x += IIf(DataGridView1.Columns(i).Visible, DataGridView1.Columns(i).Width, 0)
                If mdicColumnsVisible.ContainsKey(DataGridView1.Columns(i).Name) Then
                    If Not mdicColumnsVisible.Item(DataGridView1.Columns(i).Name) Then
                        Continue For
                    End If
                End If
                scrollingOffset = DataGridView1.HorizontalScrollingOffset
                If Len(mstrFrozenColumn) > 0 Then
                    If i <= DataGridView1.Columns(mstrFrozenColumn).Index Then
                        scrollingOffset = 0
                    ElseIf x - scrollingOffset < frozenWidth Then
                        Continue For
                    End If
                End If
                If marrSumCoulumns.Contains(DataGridView1.Columns(i).Name) Then
                    'dataView = dataTable.DefaultView
                    'dataView.RowFilter = DataGridView1.Columns(i).Name & " Is Not Null"
                    objSumValue = dataTable.Compute("Sum(" & DataGridView1.Columns(i).Name & ")", "")
                    e.Graphics.DrawString(IIf(IsDBNull(objSumValue), 0, objSumValue),
                            Me.FootSumLabel.Font, Brushes.Black, x - scrollingOffset, 3, strFormat)
                End If
            Next i
        End If
        e.Graphics.FillRectangle(Brushes.Blue, New Rectangle(0, 0, DataGridView1.RowHeadersWidth, FootSumLabel.Height))
        e.Graphics.DrawString(DataGridView1.Rows.Count & "行", Me.FootSumLabel.Font, Brushes.Black, DataGridView1.RowHeadersWidth, 3, strFormat)
    End Sub

    Private Function GetFrozenWidth() As Integer
        Dim value As Integer = DataGridView1.RowHeadersWidth
        If Len(mstrFrozenColumn) = 0 Then Return 0
        For i As Integer = 0 To DataGridView1.Columns(mstrFrozenColumn).Index
            value += IIf(DataGridView1.Columns(i).Visible, DataGridView1.Columns(i).Width, 0)
        Next
        Return value
    End Function

    Private Sub DataGridView1_CellDoubleClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        RaiseEvent RowDoubleClick(e.RowIndex)
    End Sub

    Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        If mblnIsTransZeroToBlank Then
            If IsDBNull(e.Value) Then Exit Sub
            If DataGridView1.Columns(e.ColumnIndex).ValueType = "".GetType Then Exit Sub
            If Trim(CStr(e.Value)) = "0" Then
                e.Value = DBNull.Value
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellMouseDown(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDown
        If e.Button = Windows.Forms.MouseButtons.Right Then
            If e.RowIndex >= 0 And e.RowIndex <= DataGridView1.Rows.Count - 1 _
                        And e.ColumnIndex >= 0 And e.ColumnIndex <= DataGridView1.Columns.Count - 1 Then
                DataGridView1.CurrentCell = DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex)
            End If
        End If
    End Sub

    Private Sub DataGridView1_ColumnWidthChanged(sender As Object, e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles DataGridView1.ColumnWidthChanged
        FootSumLabel.Refresh()
    End Sub

    Private Sub DataGridView1_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseClick
        Dim isFilter As Boolean = False
        Dim enabled As Boolean = True
        If e.Button = Windows.Forms.MouseButtons.Right Then
            If DataGridView1.Rows.Count <= 0 Then
                enabled = False
            End If
            For Each item As ToolStripItem In ContextMenuStrip1.Items
                item.Enabled = enabled
            Next
            If Len(mdvDataView.RowFilter) > 0 Then
                If mdvDataView.RowFilter <> mstrRelationRowFilter Then
                    isFilter = True
                End If
            End If
            Me.ContextMenuStrip1.Show(sender, e.X, e.Y)
            Me.ContextMenuStrip1.Items("取消筛选ToolStripMenuItem").Enabled = isFilter
            Me.ContextMenuStrip1.Items("取消上次筛选ToolStripMenuItem").Enabled = isFilter
        End If
    End Sub

    Private Sub DataGridView1_RowHeadersWidthChanged(sender As Object, e As System.EventArgs) Handles DataGridView1.RowHeadersWidthChanged
        FootSumLabel.Refresh()
    End Sub

    Private Sub DataGridView1_Scroll(sender As Object, e As System.Windows.Forms.ScrollEventArgs) Handles DataGridView1.Scroll
        If e.ScrollOrientation = ScrollOrientation.HorizontalScroll Then
            FootSumLabel.Refresh()
        End If
    End Sub

    Private Sub 导出到ExcelToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles 导出到ExcelToolStripMenuItem.Click
        Dim threadStart As Threading.ThreadStart = New Threading.ThreadStart(AddressOf ExportExcelRun)
        Dim thread As Threading.Thread = New Threading.Thread(threadStart)
        thread.Start()
    End Sub

    Private Sub ExportExcelRun()
        Dim toolExcel As New CToolExcel
        Dim dataTable As DataTable
        Dim colIndexExcel As Integer = 0
        Try
            dataTable = mdvDataView.ToTable
            If Not toolExcel.OpenFile() Then Exit Sub
            For i As Integer = 0 To DataGridView1.Columns.Count - 1
                If mdicColumnsVisible.ContainsKey(dataTable.Columns(i).ColumnName) Then
                    If Not mdicColumnsVisible.Item(dataTable.Columns(i).ColumnName) Then
                        Continue For
                    End If
                End If
                colIndexExcel += 1
                toolExcel.Cells(1, colIndexExcel) = DataGridView1.Columns(i).HeaderText
            Next
            colIndexExcel = 0
            For colIndex As Integer = 0 To dataTable.Columns.Count - 1
                If mdicColumnsVisible.ContainsKey(dataTable.Columns(colIndex).ColumnName) Then
                    If Not mdicColumnsVisible.Item(dataTable.Columns(colIndex).ColumnName) Then
                        Continue For
                    End If
                End If
                colIndexExcel += 1
                For rowIndex As Integer = 0 To dataTable.Rows.Count - 1
                    toolExcel.Cells(rowIndex + 2, colIndexExcel) = dataTable.Rows(rowIndex).Item(colIndex)
                Next
            Next
            toolExcel.ViewFile()
        Catch ex As Exception
            MsgBox("导出到Excel失败," & ex.Message, MsgBoxStyle.Exclamation, "系统提示")
        End Try

    End Sub

    Private Sub 排除筛选ToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles 排除筛选ToolStripMenuItem.Click
        Dim columnName As String
        Dim value As String

        columnName = DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Name
        value = IIf(IsDBNull(DataGridView1.CurrentCell.Value), "", DataGridView1.CurrentCell.Value)
        If IsDBNull(DataGridView1.CurrentCell.Value) Then
            If DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).ValueType = "".GetType Then
                mdvDataView.RowFilter = columnName & " Is not Null" & " And Len(" & columnName & ") > 0"
            Else
                mdvDataView.RowFilter = columnName & " Is not Null"
            End If
        ElseIf Len(DataGridView1.CurrentCell.Value) = 0 Then
            mdvDataView.RowFilter = columnName & " Is not Null" & " And Len(" & columnName & ") > 0"
        Else
            mdvDataView.RowFilter = columnName & " <> '" & value & "'"
        End If
        If mobjRowFilter.Count > 0 Then
            mdvDataView.RowFilter = mdvDataView.RowFilter & " And " & mobjRowFilter.Peek()
        End If
        mobjRowFilter.Push(mdvDataView.RowFilter)
        If Len(mdvDataView.RowFilter) > 0 Then
            If Len(mstrRelationRowFilter) > 0 Then
                mdvDataView.RowFilter = mdvDataView.RowFilter & " And " & mstrRelationRowFilter
            End If
        Else
            mdvDataView.RowFilter = mstrRelationRowFilter
        End If
        FootSumLabel.Refresh()
    End Sub

    Private Sub 相同筛选ToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles 相同筛选ToolStripMenuItem.Click
        Dim columnName As String
        Dim value As String
        columnName = DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Name
        value = IIf(IsDBNull(DataGridView1.CurrentCell.Value), "", DataGridView1.CurrentCell.Value)
        If IsDBNull(DataGridView1.CurrentCell.Value) Then
            If DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).ValueType = "".GetType Then
                mdvDataView.RowFilter = columnName & " Is Null " & " Or Len(" & columnName & ") = 0"
            Else
                mdvDataView.RowFilter = columnName & " Is Null "
            End If
        ElseIf Len(DataGridView1.CurrentCell.Value) = 0 Then
            mdvDataView.RowFilter = columnName & " Is Null " & " Or Len(" & columnName & ") = 0"
        Else
            mdvDataView.RowFilter = columnName & " = '" & value & "'"
        End If
        If mobjRowFilter.Count > 0 Then
            mdvDataView.RowFilter = mdvDataView.RowFilter & " And " & mobjRowFilter.Peek()
        End If
        mobjRowFilter.Push(mdvDataView.RowFilter)
        If Len(mdvDataView.RowFilter) > 0 Then
            If Len(mstrRelationRowFilter) > 0 Then
                mdvDataView.RowFilter = mdvDataView.RowFilter & " And " & mstrRelationRowFilter
            End If
        Else
            mdvDataView.RowFilter = mstrRelationRowFilter
        End If
        FootSumLabel.Refresh()
    End Sub

    Private Sub 取消筛选ToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles 取消筛选ToolStripMenuItem.Click
        mdvDataView.RowFilter = mstrRelationRowFilter
        mobjRowFilter.Clear()
        FootSumLabel.Refresh()
    End Sub

    Private Sub 取消上次筛选ToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles 取消上次筛选ToolStripMenuItem.Click
        If mobjRowFilter.Count <= 1 Then
            mdvDataView.RowFilter = mstrRelationRowFilter
            mobjRowFilter.Clear()
        Else
            mobjRowFilter.Pop()
            mdvDataView.RowFilter = mobjRowFilter.Peek() _
                        & IIf(Len(mstrRelationRowFilter) > 0, " And " & mstrRelationRowFilter, "")
        End If
        FootSumLabel.Refresh()
    End Sub

    Private Sub DataGridView1_SelectionChanged(sender As Object, e As System.EventArgs) Handles DataGridView1.SelectionChanged
        If mblnIsInit Then
            notifyObservers()
        End If
    End Sub

    Private mobjObservers As New ArrayList
    Public Sub RegisterObserver(ByRef observer As QueryGrid)
        mobjObservers.Add(observer)
        observer.RefreshGrid(Me.DataGridView1)
    End Sub

    Public Sub RemoveObserver(ByRef observer As QueryGrid)
        Dim i As Integer
        i = mobjObservers.IndexOf(observer)
        If i >= 0 Then
            mobjObservers.RemoveAt(i)
        End If
    End Sub

    Public Sub notifyObservers()
        For i As Integer = 0 To mobjObservers.Count - 1
            CType(mobjObservers(i), QueryGrid).RefreshGrid(Me.DataGridView1)
        Next
    End Sub

    Public Sub RefreshGrid(ByRef grid As DataGridView)
        Dim str1 As String
        If grid.CurrentRow Is Nothing Then
            mstrRelationRowFilter = " 1 = 2"
        Else
            str1 = grid.CurrentRow.Cells(mstrParentsColumns).Value
            mstrRelationRowFilter = mstrLocalColumns & "='" & str1 & "'"
        End If
        If Not mblnIsInit Then Exit Sub
        If mobjRowFilter.Count > 0 Then
            mdvDataView.RowFilter = mobjRowFilter.Peek & " And " & mstrRelationRowFilter
        Else
            mdvDataView.RowFilter = mstrRelationRowFilter
        End If

    End Sub
End Class
